import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;

import java.util.*;
import java.util.stream.Collectors;

/**
 * @author Chengxh
 * @Description
 * @Date Created In 9:44 2022/11/9
 * @Modified By:
 */
public class MergeStockWithManager {

    public static void main(String[] args) {
        ExcelReader reader;

//通过sheet编号获取
        reader = ExcelUtil.getReader(FileUtil.file("d:/stock.xlsx"));
        int sheetCount = reader.getSheetCount();
        Map<String, List<String>> result = new HashMap<>();
        //跳过总表.第一个sheet不用，跳过处理
        int countOfPre = 1;
        for (int sheetNum = countOfPre; sheetNum < sheetCount; sheetNum++) {
            reader.setSheet(sheetNum);
            int colNum = 1;
            while (isContinue(colNum, reader)) {
                int stockRow = 0;
                int managerRow = stockRow + 1;
                while (CollUtil.isNotEmpty(reader.read(stockRow, stockRow))) {
                    List<String> list = reader.readColumn(colNum, stockRow, managerRow).stream().map(x -> (String) x).filter(x -> StrUtil.isNotEmpty(x)).collect(Collectors.toList());
                    if (CollUtil.isNotEmpty(list)) {
                        String stockName = (String) list.get(0);
                        List<String> managers = new LinkedList<>();
                        if (list.size() == 2) {
                            managers = StrUtil.split((String) list.get(1), StrUtil.COMMA);
                        }
                        if (CollUtil.isNotEmpty(result.get(stockName))) {
                            List<String> beforeManagers = result.get(stockName);
                            managers.addAll(beforeManagers);
                        }
                        result.put(stockName, managers);
                    }
                    stockRow = stockRow + 2;
                    managerRow = managerRow + 2;
                }
                colNum++;
            }
            result.size();
        }
        reader.close();
        ExcelWriter writer = new ExcelWriter("d:/stock.xlsx", "afterMerge");
        LinkedList<List<String>> lists = new LinkedList<>();
        result.forEach((x, y) -> {
            ArrayList<String> row = CollUtil.newArrayList(x, StrUtil.join(StrUtil.COMMA, y));
            lists.add(row);
        });

        List<List<String>> rows = CollUtil.newArrayList(lists);
        writer.write(rows);
        writer.close();

    }

    private static boolean isContinue(int colNum, ExcelReader reader) {
        List<String> list1 = reader.readColumn(colNum, 0).stream().map(x -> (String) x).collect(Collectors.toList());
        list1.removeIf(String::isEmpty);

        return CollUtil.isNotEmpty(list1);
    }
}
